﻿/* 
 * 
 *    Project           A report class 
 *    Module Name       Excel Report 
 *    Author            Justin.Gu 
 *    Creation Date     [12/10/2016]      
 *     
 */
using System;
using System.Data;
using System.Drawing;
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.Drawing.Chart;
using OfficeOpenXml.Style;
using System.Collections;
using System.Collections.Generic;
using SPCLib;
using System.Configuration;
using System.Text.RegularExpressions;
using System.Xml;
using System.Linq;
using EHR18.Models;
using OfficeOpenXml.Table;

namespace Common
{
    public partial class Recordsaver
    {
        private static string strBeginTime = "";
        private static string strEndTime = "";
        private static List<CPKData> GetCPKDatas(IEnumerable<TABLE_T6418> table)
        {
            List<CPKData> cpkDatas = new List<CPKData>();
            if (table.Where(t => !t.LS_VALUE.IsNullOrEmpty()).Count() > 0)
            {
                double value;
                string Max =table.Where(t => !t.LS_MAX.IsNullOrEmpty()).Select(t => t.LS_MAX).First();
                string Min= table.Where(t => !t.LS_MIN.IsNullOrEmpty()).Select(t => t.LS_MIN).First();
                CPKData data = new CPKData();
                string Name = "LS";
                //把VALue1里面的数据保存到List中
                List<double> values = new List<double>();
                var query = table.Where(t => !t.LS_VALUE.IsNullOrEmpty()).Select(t => t.LS_VALUE);
                foreach(var v in query)
                {
                    double val;
                    string strVal = v;
                    if (double.TryParse(strVal, out val))
                    {
                        values.Add(val);
                    }
                }
                List<ReportConfigItem> rc =GenneralConfig.instance.module.ReportConfigItems;
                if (rc!=null
                    && rc.Where(t=>t.Name=="LS").Count()>0)
                {
                    var rci = rc.Where(t => t.Name == "LS").First();
                    data.LSL = rci.LSL;
                    data.USL = rci.USL;
                    data.MarkVals = rci.MarkVals;
                    data.UseMarkVals = rci.UseMarkVal;
                }
                else
                {
                    double tmp_val;
                    double.TryParse(Min, out tmp_val);
                    data.LSL = tmp_val;
                    double.TryParse(Max, out tmp_val);
                    data.USL = tmp_val;
                }
                data.Datas = values;
                data.ItemName = Name;
                cpkDatas.Add(data);
            }
            if (table.Where(t => !t.R1_VALUE.IsNullOrEmpty()).Count() > 0)
            {
                double value;
                string Max = table.Where(t => !t.R1_MAX.IsNullOrEmpty()).Select(t => t.R1_MAX).First();
                string Min = table.Where(t => !t.R1_MAX.IsNullOrEmpty()).Select(t => t.R1_MIN).First();
                CPKData data = new CPKData();
                string Name = "R1";
                //把VALue1里面的数据保存到List中
                List<double> values = new List<double>();
                var query = table.Where(t => !t.R1_VALUE.IsNullOrEmpty()).Select(t => t.R1_VALUE);
                foreach (var v in query)
                {
                    double val;
                    string strVal = v;
                    if (double.TryParse(strVal, out val))
                    {
                        values.Add(val);
                    }
                    else
                    {
                        values.Add(double.MaxValue);
                    }
                }
                List<ReportConfigItem> rc = GenneralConfig.instance.module.ReportConfigItems;
                if (rc != null
                    && rc.Where(t => t.Name == "R1").Count() > 0)
                {
                    var rci = rc.Where(t => t.Name == "R1").First();
                    data.LSL = rci.LSL;
                    data.USL = rci.USL;
                    data.MarkVals = rci.MarkVals;
                    data.UseMarkVals = rci.UseMarkVal;
                }
                else
                {
                    double tmp_val;
                    double.TryParse(Min, out tmp_val);
                    data.LSL = tmp_val;
                    double.TryParse(Max, out tmp_val);
                    data.USL = tmp_val;
                }
                data.Datas = values;
                data.ItemName = Name;
                cpkDatas.Add(data);
            }
            if (table.Where(t => !t.SURG_VALUE.IsNullOrEmpty()).Count() > 0)
            {
                double value;
                string Max = table.Where(t => !t.SURG_MAX.IsNullOrEmpty()).Select(t => t.SURG_MAX).First();
                string Min = table.Where(t => !t.SURG_MIN.IsNullOrEmpty()).Select(t => t.SURG_MIN).First();
                CPKData data = new CPKData();
                string Name = "SURG";
                //把VALue1里面的数据保存到List中
                List<double> values = new List<double>();
                var query = table.Where(t => !t.SURG_VALUE.IsNullOrEmpty()).Select(t => t.SURG_VALUE);
                foreach (var v in query)
                {
                    double val;
                    string strVal = v;
                    if (double.TryParse(strVal, out val))
                    {
                        values.Add(val);
                    }
                }
                List<ReportConfigItem> rc = GenneralConfig.instance.module.ReportConfigItems;
                if (rc != null
                    && rc.Where(t => t.Name == "SURG").Count() > 0)
                {
                    var rci = rc.Where(t => t.Name == "SURG").First();
                    data.LSL = rci.LSL;
                    data.USL = rci.USL;
                    data.MarkVals = rci.MarkVals;
                    data.UseMarkVals = rci.UseMarkVal;
                }
                else
                {
                    double tmp_val;
                    double.TryParse(Min, out tmp_val);
                    data.LSL = tmp_val;
                    double.TryParse(Max, out tmp_val);
                    data.USL = tmp_val;
                }
                data.Datas = values;
                data.ItemName = Name;
                cpkDatas.Add(data);
            }
            if (table.Where(t => !t.TR1_VALUE.IsNullOrEmpty()).Count() > 0)
            {
                double value;
                string Max = table.Where(t => !t.TR1_MAX.IsNullOrEmpty()).Select(t => t.TR1_MAX).First();
                string Min = table.Where(t => !t.TR1_MIN.IsNullOrEmpty()).Select(t => t.TR1_MIN).First();
                CPKData data = new CPKData();
                string Name = "TR1";
                //把VALue1里面的数据保存到List中
                List<double> values = new List<double>();
                var query = table.Where(t => !t.TR1_VALUE.IsNullOrEmpty()).Select(t => t.TR1_VALUE);
                foreach (var v in query)
                {
                    double val;
                    string strVal = v;
                    if (double.TryParse(strVal, out val))
                    {
                        values.Add(val);
                    }
                }
                List<ReportConfigItem> rc = GenneralConfig.instance.module.ReportConfigItems;
                if (rc != null
                    && rc.Where(t => t.Name == "TR1").Count() > 0)
                {
                    var rci = rc.Where(t => t.Name == "TR1").First();
                    data.LSL = rci.LSL;
                    data.USL = rci.USL;
                    data.MarkVals = rci.MarkVals;
                    data.UseMarkVals = rci.UseMarkVal;
                }
                else
                {
                    double tmp_val;
                    double.TryParse(Min, out tmp_val);
                    data.LSL = tmp_val;
                    double.TryParse(Max, out tmp_val);
                    data.USL = tmp_val;
                }
                data.Datas = values;
                data.ItemName = Name;
                cpkDatas.Add(data);
            }
            if (table.Where(t => !t.TR2_VALUE.IsNullOrEmpty()).Count() > 0)
            {
                double value;
                string Max = table.Where(t => !t.TR2_MAX.IsNullOrEmpty()).Select(t => t.TR2_MAX).First();
                string Min = table.Where(t =>!t.TR2_MIN.IsNullOrEmpty()).Select(t => t.TR2_MIN).First();
                CPKData data = new CPKData();
                string Name = "TR2";
                //把VALue1里面的数据保存到List中
                List<double> values = new List<double>();
                var query = table.Where(t => !t.TR2_VALUE.IsNullOrEmpty()).Select(t => t.TR2_VALUE);
                foreach (var v in query)
                {
                    double val;
                    string strVal = v;
                    if (double.TryParse(strVal, out val))
                    {
                        values.Add(val);
                    }
                }
                List<ReportConfigItem> rc = GenneralConfig.instance.module.ReportConfigItems;
                if (rc != null
                    && rc.Where(t => t.Name == "TR2").Count() > 0)
                {
                    var rci = rc.Where(t => t.Name == "TR2").First();
                    data.LSL = rci.LSL;
                    data.USL = rci.USL;
                    data.MarkVals = rci.MarkVals;
                    data.UseMarkVals = rci.UseMarkVal;
                }
                else
                {
                    double tmp_val;
                    double.TryParse(Min, out tmp_val);
                    data.LSL = tmp_val;
                    double.TryParse(Max, out tmp_val);
                    data.USL = tmp_val;
                }
                data.Datas = values;
                data.ItemName = Name;
                cpkDatas.Add(data);
            }
            return cpkDatas;
        }

        public static List<string> getRESULTCol(DataTable dt)
        {
            List<string> colNames = new List<string>();
            foreach (DataColumn cor in dt.Columns)
            {
                if (cor.ColumnName.ToUpper().Contains("RESULT"))
                {
                    colNames.Add(cor.ColumnName);
                }
            }
            return colNames;
        }

        public static void SaveFailRateStaticXml(Dictionary<string, FailRateStatistic> dic, string beginTime, string endTime)
        {
            if (!File.Exists("FailRateStatic.xml"))
            {
                Common.XMLHelper.CreateXmlDocument("FailRateStatic.xml", "Statistics", "1.0", "UTF-8", null);
            }
            XmlDocument doc = new XmlDocument();
            doc.Load("FailRateStatic.xml");
            XmlNode root = doc.SelectSingleNode("Statistics");
            XmlElement xe = doc.CreateElement(string.Format("T{0}-T{1}", beginTime.Replace(" ", "").Replace(":", "").Replace("-", ""), endTime.Replace(" ", "").Replace(":", "").Replace("-", "")));
            foreach (var kv in dic)
            {
                XmlElement xe1 = doc.CreateElement(kv.Key);
                //     xe1.InnerText=kv.Key;
                XmlElement xe2 = doc.CreateElement("PassQty");
                xe2.InnerText = kv.Value.PassCount.ToString();
                XmlElement xe3 = doc.CreateElement("FailQty");
                xe3.InnerText = kv.Value.FailCount.ToString();
                XmlElement xe4 = doc.CreateElement("TotalQty");
                xe4.InnerText = kv.Value.TotalCount.ToString();
                XmlElement xe5 = doc.CreateElement("FailRate");
                xe5.InnerText = kv.Value.FailRate.ToString("P");
                xe.AppendChild(xe1);
                xe1.AppendChild(xe2);
                xe1.AppendChild(xe3);
                xe1.AppendChild(xe4);
                xe1.AppendChild(xe5);
            }
            root.AppendChild(xe);
            doc.Save("FailRateStatic.xml");
        }

        public static Dictionary<string, FailRateStatistic> GetFailRate(IEnumerable<TABLE_T6418> table)
        {
            Dictionary<string, FailRateStatistic> dic = new Dictionary<string, FailRateStatistic>();
            dic.Add("耐压1", new FailRateStatistic() { FailCount = table.Where(t => t.HIPOT1 == "FAIL").Count(), TotalCount = table.Where(t => (string.IsNullOrEmpty(t.HIPOT1) == false)).Count() });
            dic.Add("耐压2", new FailRateStatistic() { FailCount = table.Where(t => t.HIPOT2 == "FAIL").Count(), TotalCount = table.Where(t => (string.IsNullOrEmpty(t.HIPOT2) == false)).Count() });
            dic.Add("LS", new FailRateStatistic() { FailCount = table.Where(t => t.LS_RESULT == "FAIL").Count(), TotalCount = table.Where(t => (string.IsNullOrEmpty(t.LS_RESULT) == false)).Count() });
            dic.Add("CTY", new FailRateStatistic() { FailCount = table.Where(t => t.CTY_RESULT == "FAIL").Count(), TotalCount = table.Where(t => (string.IsNullOrEmpty(t.CTY_RESULT) == false)).Count() });
            dic.Add("TR1", new FailRateStatistic() { FailCount = table.Where(t => t.TR1_RESULT == "FAIL").Count(), TotalCount = table.Where(t => (string.IsNullOrEmpty(t.TR1_RESULT) == false)).Count() });
            dic.Add("TR2", new FailRateStatistic() { FailCount = table.Where(t => t.TR2_RESULT == "FAIL").Count(), TotalCount = table.Where(t => (string.IsNullOrEmpty(t.TR2_RESULT) == false)).Count() });
            dic.Add("SURG", new FailRateStatistic() { FailCount = table.Where(t => t.SURG_RESULT == "FAIL").Count(), TotalCount = table.Where(t => (string.IsNullOrEmpty(t.SURG_RESULT) == false)).Count() });
            if (table.Where(t =>(!string.IsNullOrEmpty(t.R1_VALUE)) &&t.R1_VALUE.Contains("PASS")).Count() > 0)
            {
                dic.Add("R1", new FailRateStatistic() { FailCount = table.Where(t => t.R1_RESULT == "FAIL").Count(), TotalCount = table.Where(t => (string.IsNullOrEmpty(t.R1_RESULT) == false)).Count() });
            }
            if (table.Where(t => (!string.IsNullOrEmpty(t.Cam_RESULT)) && t.Cam_RESULT.Contains("PASS")).Count() > 0)
            {
                dic.Add("图像", new FailRateStatistic() { FailCount = table.Where(t => t.Cam_RESULT == "FAIL").Count(), TotalCount = table.Where(t => (string.IsNullOrEmpty(t.Cam_RESULT) == false)).Count() });
            }
            dic.Add("合计", new FailRateStatistic() { FailCount = table.Where(t => t.FINAL_RESULT == "FAIL").Count(), TotalCount = table.Count() });
            return dic;
        }

        public static void Saveexcel(IEnumerable<TABLE_T6418> table,DataTable dt, DateTime BeginTime, DateTime EndTime, string templateFilePath)
        {

            string fileName = "ExcelReport-" + DateTime.Now.ToString("yyyy_MM_dd_HHmmss") + ".xlsx";
            string Dir = Path.Combine(Product.DATA_DIR,"Excel Report");
            strBeginTime = BeginTime.ToString("yyyy-MM-dd HH:mm:ss");
            strEndTime = EndTime.ToString("yyyy-MM-dd HH:mm:ss");
            if (!Directory.Exists(Dir))
            {
                Directory.CreateDirectory(Dir);
            }
            FileInfo file = new FileInfo(Dir + "\\" + fileName);
            using (ExcelPackage package = new ExcelPackage(file, new FileInfo(templateFilePath)))
            {
                //  ExcelWorksheet ReportSheet = package.Workbook.Worksheets.Add("Report");
                ExcelWorksheet ReportSheet = package.Workbook.Worksheets["Report"];
                //   package.Workbook.
                ReportSheet.View.ShowGridLines = false;
                ReportSheet.Cells[1, 1].Value = string.Format("{0} Test Report ({1}-{2})", GenneralConfig.instance.CurrentModel, BeginTime, EndTime);
                SetReportTitle(strBeginTime, strEndTime, ReportSheet);
                int irow = 3;
                irow = SaveFailRateStatistics(table, ReportSheet, irow);
                irow++;
                irow = SaveDetailStatistics(table, ReportSheet, irow);
                ExcelWorksheet ws = package.Workbook.Worksheets["Data"];
                ws.Cells["A1"].LoadFromDataTable(dt, true);
                package.Save(); //保存文件   
            }
        }

        private static void SetReportTitle(string BeginTime, string EndTime, ExcelWorksheet ReportSheet)
        {
            ExcelRange range = ReportSheet.Cells[1, 1, 1, 12];
            range.Style.Font.Bold = true;
            range.Style.Font.Size = 16F;
            range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            range.Style.Font.Name = "Microsoft Sans Serif";
            range.Merge = true;
            range.Value = string.Format("{0} Test Report", ConfigurationManager.AppSettings["CurrentModel"]);
            ExcelRange range1 = ReportSheet.Cells[2, 1, 2, 12];
            range1.Merge = true;
            range1.Value = string.Format("{0}~{1}", BeginTime, EndTime);
            range1.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
        }

        private static void SaveDatas(DataTable dt, ExcelWorksheet worksheet)
        {
            DataTable dataTest = dt;
            RemoveSomeCorlumns(dataTest);

            //设置表头
            for (int col = 1; col <= dataTest.Columns.Count; col++)
            {
                worksheet.Cells[1, col].Value = dataTest.Columns[col - 1].ColumnName;
            }
            //设置数据  

            for (int row = 1; row <= dataTest.Rows.Count; row++)
            {

                for (int col = 1; col <= dataTest.Columns.Count; col++)
                {
                    string strValue = dataTest.Rows[row - 1][col - 1].ToString();
                    worksheet.Cells[row + 1, col].Value = strValue;
                }
            }
        }

        private static void RemoveSomeCorlumns(DataTable dataTest)
        {
            List<string> corRemoveList = new List<string>();
            for (int i = 0; i < dataTest.Columns.Count; i++)
            {
                string corName = dataTest.Columns[i].ColumnName;
                if (corName.Contains("ID") || (corName.Contains("VALUE2") || Regex.IsMatch(corName, @"MODEL\d")))
                {
                    corRemoveList.Add(corName);
                }
                if (corName.Contains("MIN"))
                {
                    string val = "";
                    foreach (DataRow row in dataTest.Rows)
                    {
                        val = row[corName].ToString();
                        if (!string.IsNullOrEmpty(val))
                        {
                            break;
                        }
                    }
                    if (string.IsNullOrEmpty(val) || val.Contains("NA"))
                    {
                        corRemoveList.Add(corName);
                        string strMAX = Regex.Replace(corName, "MIN", "MAX");
                        corRemoveList.Add(strMAX);
                        string strVALUE = Regex.Replace(corName, "MIN", "VALUE1");
                        corRemoveList.Add(strVALUE);
                    }
                }
            }
            foreach (string name in corRemoveList)
            {
                dataTest.Columns.Remove(name);
            }
            dataTest.AcceptChanges();
        }

        private static int SaveDetailStatistics(IEnumerable<TABLE_T6418> table, ExcelWorksheet ReportSheet, int irow)
        {
            int rowBegin = irow;
            List<CPKData> cpkDatas = GetCPKDatas(table);
            List<ExcelChart> charts = new List<ExcelChart>();
            for (int idata = 0; idata < cpkDatas.Count; idata++)
            {
                int iRowBegin = irow;
                ReportSheet.Cells[irow, 1].Value = cpkDatas[idata].ItemName;
                irow++;
                Dictionary<string, double> dic = cpkDatas[idata].GetCPKDictionary();
                int rowCount = 0;
                foreach (var kp in dic)
                {
                    int row = irow;
                    int col = 1 + rowCount * 2;
                 
                    ExcelSetValueAndStyle(ReportSheet.Cells[row, col], kp.Key);
                    ExcelSetValueAndStyle(ReportSheet.Cells[row, col + 1], kp.Value);
                    if (++rowCount > 5)
                    {
                        irow++;
                        rowCount = 0;
                    }
                }
                irow++;
                ReportSheet.Cells[irow, 1].Value = "Statistics by Groups:";
                irow++;
                List<GroupItem> gis = cpkDatas[idata].getGroupItems();
                //            ReportSheet.Cells[irow , 1].Value = "Item";
                ExcelSetValueAndStyle(ReportSheet.Cells[irow, 1], "Group");
                ExcelSetValueAndStyle(ReportSheet.Cells[irow + 1, 1], "Qty");
                ExcelSetValueAndStyle(ReportSheet.Cells[irow + 2, 1], "Percent");
                for (int i = 0; i < gis.Count; i++)
                {
                    ExcelSetValueAndStyle(ReportSheet.Cells[irow, 2 + i], gis[i].GroupTitle);
                    ExcelSetValueAndStyle(ReportSheet.Cells[irow + 1, 2 + i], gis[i].qty);
                    ReportSheet.Cells[irow + 2, 2 + i].Style.Numberformat.Format = @"0.00%";//设置为百分比显示
                    ExcelSetValueAndStyle(ReportSheet.Cells[irow + 2, 2 + i], gis[i].Percent);
                }
                irow = irow + 3;
                ExcelSetValueAndStyle(ReportSheet.Cells[irow, 1], "System Fail");
                ExcelSetValueAndStyle(ReportSheet.Cells[irow, 2], gis[0].systemFail);
                ExcelSetValueAndStyle(ReportSheet.Cells[irow, 3], gis[0].systemFail * 1.0 / gis[0].total);
                ReportSheet.Cells[irow, 3].Style.Numberformat.Format = @"0.00%";
                ExcelChart bar = ReportSheet.Drawings.AddChart(cpkDatas[idata].ItemName, eChartType.ColumnClustered);
                var ser = bar.Series.Add(ReportSheet.Cells[irow - 1, 2, irow - 1, 12], ReportSheet.Cells[irow - 3, 2, irow - 3, 12]);
                ser.Header = "占比";
                bar.Legend.Position = eLegendPosition.Right;
                irow++;

                //     bar.Legend.Add();
                bar.Title.Text = cpkDatas[idata].ItemName + "数据分布"; //设置图表的名称   
                bar.SetPosition(iRowBegin, 0, 13, 0);
                bar.SetSize(410, 240); //设置图表大小            
                bar.ShowHiddenData = true;
                charts.Add(bar);
                irow++;
            }
            int j = 0;
            for (int i = 0; i < charts.Count; i++)
            {
                int c = i % 2;
                j++;
                charts[i].SetPosition(irow, 0, 6 * c, 0);
                if (j > 1)
                {
                    irow = irow + 15;
                    j = 0;
                }

            }
            return irow;
        }
        
        private static void ExcelSetValueAndStyle(ExcelRange range, object Value)
        {
            range.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
            range.AutoFitColumns(10, 80);
            range.Value = Value;
        }

        private static int SaveFailRateStatistics(IEnumerable<TABLE_T6418> table, ExcelWorksheet ReportSheet, int irow)
        {
            int rowBegin = irow;
            Dictionary<string, FailRateStatistic> dicFailRate = GetFailRate(table);
            SaveFailRateStaticXml(dicFailRate, strBeginTime, strEndTime);
            ExcelSetValueAndStyle(ReportSheet.Cells[irow, 1], "Item");
            ExcelSetValueAndStyle(ReportSheet.Cells[irow, 2], "Fail Qty");
            ExcelSetValueAndStyle(ReportSheet.Cells[irow, 3], "Pass Qty");
            ExcelSetValueAndStyle(ReportSheet.Cells[irow, 4], "Total Qty");
            ExcelSetValueAndStyle(ReportSheet.Cells[irow, 5], "Fail Rate");
            irow++;
            foreach (var kv in dicFailRate)
            {
                ExcelSetValueAndStyle(ReportSheet.Cells[irow, 1], kv.Key);
                ExcelSetValueAndStyle(ReportSheet.Cells[irow, 2], kv.Value.FailCount);
                ExcelSetValueAndStyle(ReportSheet.Cells[irow, 3], kv.Value.PassCount);
                ExcelSetValueAndStyle(ReportSheet.Cells[irow, 4], kv.Value.TotalCount);
                ExcelSetValueAndStyle(ReportSheet.Cells[irow, 5], kv.Value.FailRate.ToString("P"));
                irow++;
            }
            return irow;
        }
    }
}